﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Golos.Helper;
using Golos.Polls.Configuration;

namespace Golos.Polls.PollCache
{
    public partial class Cache
    {
        public List<Guid> GetTop10Question()
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(@"select top 10 PollQuestion.ID, COUNT(PollUserAnswer.UserID) from PollQuestion with (nolock)
                                                            LEFT JOIN PollAnswer with (nolock) ON PollAnswer.QuestionID = PollQuestion.ID
                                                            LEFT JOIN PollUserAnswer with (nolock) ON PollUserAnswer.AnswerID = PollAnswer.ID
                                                            where (endDate > GETUTCDATE() or endDate is null) and IsDeleted = 0
                                                            group by PollQuestion.ID
                                                            order by COUNT(PollUserAnswer.UserID) desc", connection);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                            return new List<Guid>();
                        List<Guid> result = new List<Guid>();
                        while (reader.Read())
                        {
                            Guid questionId = reader.GetGuid(0);
                            result.Add(questionId);
                        }
                        return result;
                    }
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    throw;
                }
            }
        }

        private void GetPollCacheFromDb()
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                try
                {
                    connection.Open();
                    questionPolls = new Dictionary<Guid, PollInfo>();
                    //userPolls = new Dictionary<Guid, List<UserAnswerInfo>>();
                    answerPropertiesCache = new Dictionary<Guid, List<AnswerProperties>>();
                    voicesCountCache = new Dictionary<Guid, Dictionary<Guid, int>>();
                    questionUserAnswers = new Dictionary<Guid, Dictionary<Guid, UserAnswerInfo>>();
                    userOwnerQuestions = new Dictionary<Guid, List<Guid>>();
                    userVotedQuestions = new Dictionary<Guid, List<Guid>>();
                    SqlCommand command = new SqlCommand("SELECT [ID], [Question], [OwnerID], [EndDate],[IsDeleted] FROM [PollQuestion]", connection);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                            return;
                        while (reader.Read())
                        {
                            Guid questionId = reader.GetGuid(0);
                            string questionText = reader.GetString(1);
                            Guid userId = reader.GetGuid(2);
                            DateTime? endDate = reader.IsDBNull(3) ? (DateTime?) null : reader.GetDateTime(3);
                            bool isDeleted = reader.GetBoolean(4);
                            PollInfo pollInfo = new PollInfo(questionId, userId, endDate, questionText, isDeleted);
                            questionPolls.Add(pollInfo.Id, pollInfo);
                            if(!userOwnerQuestions.ContainsKey(userId))
                                userOwnerQuestions.Add(userId, new List<Guid>());
                            userOwnerQuestions[userId].Add(questionId);
                            questionUserAnswers.Add(questionId, new Dictionary<Guid, UserAnswerInfo>());
                            voicesCountCache.Add(questionId, new Dictionary<Guid, int>());
                        }
                    }
                    foreach (var questionId in questionPolls.Keys)
                    {
                        command = new SqlCommand(string.Format("SELECT [ID], [Answer], [OrderNumber] FROM [PollAnswer] WHERE [QuestionID]='{0}'", questionId), connection);
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (!reader.HasRows)
                                continue;
                            while (reader.Read())
                            {
                                Guid answerId = reader.GetGuid(0);
                                string answerText = reader.GetString(1);
                                int orderNumber = reader.GetInt32(2);
                                Answer answer = new Answer(answerId, answerText, orderNumber);
                                questionPolls[questionId].AddAnswer(answer);
                            }
                        }
                        foreach (Guid answerId in questionPolls[questionId].Answers.Keys)
                        {
                            command = new SqlCommand(string.Format("SELECT [UserID], [UtcDateTime] FROM [PollUserAnswer] WHERE [AnswerID]='{0}'", answerId), connection);
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                if (!reader.HasRows)
                                    continue;
                                while (reader.Read())
                                {
                                    Guid userId = reader.GetGuid(0);
                                    DateTime utcDateTime = reader.GetDateTime(1);
                                    UserAnswerInfo userAnswerInfo = new UserAnswerInfo(userId, questionId, answerId, utcDateTime);
                                   /* if(!userPolls.ContainsKey(userId))
                                        userPolls.Add(userId, new List<UserAnswerInfo>());
                                    userPolls[userId].Add(userAnswerInfo);*/
                                    if(!voicesCountCache[questionId].ContainsKey(answerId))
                                        voicesCountCache[questionId].Add(answerId, 0);
                                    voicesCountCache[questionId][answerId]++;
                                    if (!questionUserAnswers[questionId].ContainsKey(userId))
                                        questionUserAnswers[questionId].Add(userId, userAnswerInfo);
                                    else
                                        questionUserAnswers[questionId][userId] = userAnswerInfo;
                                    if (!userVotedQuestions.ContainsKey(userId))
                                        userVotedQuestions.Add(userId, new List<Guid>());
                                    userVotedQuestions[userId].Add(questionId);
                                }
                            }
                            command = new SqlCommand(string.Format("SELECT [ip] ,[sex] FROM [PollUserAnswerProperties] WHERE [AnswerID]='{0}'", answerId), connection);
                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                if (!reader.HasRows)
                                    continue;
                                while (reader.Read())
                                {
                                    string ip = reader.GetString(0);
                                    bool sex = reader.GetBoolean(1);
                                    if(!answerPropertiesCache.ContainsKey(answerId))
                                        answerPropertiesCache.Add(answerId, new List<AnswerProperties>());
                                    answerPropertiesCache[answerId].Add(new AnswerProperties(answerId, ip, sex));
                                }
                            }
                        }
                    }
                    
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    throw;
                }
            }
        }

        private bool SaveNewPollInDb(PollInfo pollInfo)
        {
            /*
             INSERT INTO [PollQuestion] ([ID],[Question],[OwnerID],[EndDate]) VALUES (<ID, uniqueidentifier,>, <Question, nvarchar(2000),>, <OwnerID, uniqueidentifier,>, <EndDate, date,>)

                INSERT INTO [PollAnswer] ([ID],[QuestionID],[Answer]) VALUES (<ID, uniqueidentifier,> ,<QuestionID, uniqueidentifier,> ,<Answer, nvarchar(2000),>)
             */
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand("INSERT INTO [PollQuestion] ([ID],[Question],[OwnerID],[EndDate]) VALUES (@questionId, @questionText, @ownerUserId, @endDate)", connection, transaction);
                    command.Parameters.Add("@questionId", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@questionText", SqlDbType.VarChar);
                    command.Parameters.Add("@ownerUserId", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@endDate", SqlDbType.DateTime);
                    command.Parameters["@questionId"].Value = pollInfo.Id;
                    command.Parameters["@questionText"].Value = pollInfo.QuestionText;
                    command.Parameters["@ownerUserId"].Value = pollInfo.OwnerUserId;
                    if (pollInfo.EndDate.HasValue)
                        command.Parameters["@endDate"].Value = pollInfo.EndDate.Value;
                    else
                        command.Parameters["@endDate"].Value = DBNull.Value;
                    command.ExecuteNonQuery();
                    foreach (var answerId in pollInfo.Answers.Keys)
                    {
                        command = new SqlCommand("INSERT INTO [PollAnswer] ([ID],[QuestionID],[Answer],[OrderNumber]) VALUES (@answerId ,@questionId ,@answerText,@orderNumber)", connection, transaction);
                        command.Parameters.Add("@answerId", SqlDbType.UniqueIdentifier);
                        command.Parameters.Add("@questionId", SqlDbType.UniqueIdentifier);
                        command.Parameters.Add("@answerText", SqlDbType.VarChar);
                        command.Parameters.Add("@orderNumber", SqlDbType.Int);
                        command.Parameters["@answerId"].Value = answerId;
                        command.Parameters["@questionId"].Value = pollInfo.Id;
                        command.Parameters["@answerText"].Value = pollInfo.Answers[answerId].AnswerText;
                        command.Parameters["@orderNumber"].Value = pollInfo.Answers[answerId].OrderNumber;
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }
            }
        }

        private bool SaveUserVoteInDb(UserAnswerInfo userAnswerInfo, AnswerProperties answerProperties)
        {
            /*
             INSERT INTO [PollUserAnswer] ([UserID],[AnswerID],[UtcDateTime]) VALUES (<UserID, uniqueidentifier,> ,<AnswerID, uniqueidentifier,> ,<UtcDateTime, datetime,>)
             INSERT INTO [PollUserAnswerProperties] ([AnswerID], [ip],[sex]) VALUES (<AnswerID, uniqueidentifier,> ,<ip, varchar(50),> ,<sex, bit,>)
             */
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand("INSERT INTO [PollUserAnswer] ([UserID],[AnswerID],[UtcDateTime]) VALUES (@userId , @answerId, @utcDateTime)", connection, transaction);
                    command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@answerId", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@utcDateTime", SqlDbType.DateTime);
                    command.Parameters["@userId"].Value = userAnswerInfo.UserId;
                    command.Parameters["@answerId"].Value = userAnswerInfo.AnswerId;
                    command.Parameters["@utcDateTime"].Value = userAnswerInfo.UtcDateTime;
                    command.ExecuteNonQuery();
                    command = new SqlCommand("INSERT INTO [PollUserAnswerProperties] ([AnswerID], [ip],[sex]) VALUES (@answerId,@ip ,@sex)", connection, transaction);
                    command.Parameters.Add("@answerId", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@ip", SqlDbType.VarChar);
                    command.Parameters.Add("@sex", SqlDbType.Bit);
                    command.Parameters["@answerId"].Value = answerProperties.AnswerId;
                    command.Parameters["@ip"].Value = answerProperties.Ip;
                    command.Parameters["@sex"].Value = answerProperties.Sex;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }
            }
        }

        public List<Guid> SearchPollInDb(string keyword)
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(string.Format(@"select distinct PollQuestion.ID from PollQuestion with (nolock)
                                                            LEFT JOIN PollAnswer with (nolock) ON PollAnswer.QuestionID = PollQuestion.ID
                                                            WHERE (PollQuestion.Question like '%{0}%' or PollAnswer.Answer like '%{0}%') and IsDeleted = 0", keyword), connection);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                            return new List<Guid>();
                        List<Guid> result = new List<Guid>();
                        while (reader.Read())
                        {
                            Guid questionId = reader.GetGuid(0);
                            result.Add(questionId);
                        }
                        return result;
                    }
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    throw;
                }
            }
        }

        private bool MarkQuestionAsDeletedInDb(Guid questionId)
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand("UPDATE [PollQuestion] SET [IsDeleted] = 1 WHERE ID=@questionId", connection, transaction);
                    command.Parameters.Add("@questionId", SqlDbType.UniqueIdentifier);
                    command.Parameters["@questionId"].Value = questionId;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }
            }
        }

        private bool MergeUserPollInDb(Guid userId, List<Guid> mergedUserIds)
        {
            /*
             update [PollQuestion] SET [OwnerID] = 'userid' WHERE [OwnerID] in ('mUserId1', 'mUserId2')
             DELETE FROM PollUserAnswer WHERE UserID in ('mUserId1', 'mUserId2') and answerId in (select answerId from PollUserAnswer where UserID='userid')
             UPDATE PollUserAnswer SET UserID = 'userid' WHERE UserID in ('mUserId1', 'mUserId2')
             */
            string mergeUsersId = "";
            foreach (Guid mUserId in mergedUserIds)
            {
                mergeUsersId += string.Format("'{0}',", mUserId);
            }
            mergeUsersId.TrimEnd(new char[] {','});
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand(string.Format("update [PollQuestion] SET [OwnerID] = @userId WHERE [OwnerID] in ({0})", mergeUsersId), connection, transaction);
                    command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
                    command.Parameters["@userId"].Value = userId;
                    command.ExecuteNonQuery();
                    command = new SqlCommand(string.Format("DELETE FROM PollUserAnswer WHERE UserID in ({0}) and answerId in (select answerId from PollUserAnswer where UserID=@userId)", mergeUsersId), connection, transaction);
                    command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
                    command.Parameters["@userId"].Value = userId;
                    command = new SqlCommand(string.Format("UPDATE PollUserAnswer SET UserID = @userId WHERE UserID in ({0})", mergeUsersId), connection, transaction);
                    command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier);
                    command.Parameters["@userId"].Value = userId;
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }
            }
        }
    }
}
